This appendix contains the following topics:
Introduction to Additional Inventory Control Utilities
Recalculate Inventory Quantities
Recalculate History Quantities
Recalculate Inventory Quantities
All PBS Utilities have the functions of export and restore. Some also have the functions of rebuild, extract and remake from an extract file. These functions are described in the PBS Administration documentation. The Inventory Control Utilities also include special functions related to controlling inventory data. These are described here and include:
6. Recalculate inventory quantities
7. Rebuild item keywords
8. Rebuild serial keywords
9. Recalculate history quantities
10. Create new inventory history
The Inventory Control utilities are available from the Windows Start menu (if those start menu selections are installed on your system). From the Windows Start menu:
Click
Programs->Passport Business Solutions->File Utilities->IC File Utilities
When accessing the utilities in UNIX, Linux or from a Windows command prompt, navigate to the top-level PBS folder and type in icutil.
This section contains information on the recalculating of inventory quantities.
The recalculate inventory quantities selection enables you to set several quantity-type fields (such as quantity on hand) in I/C to correct amounts. The I/C synchronization program updates four files/tables:
TRACKING (Lot and Serial information)
The status code flag of the serial number records is updated to show which serial numbers are committed. The quantity committed of the lot number records is updated.
ITEMS
All five quantity fields (on hand, committed, on back order, on order, and on work order) and average cost are updated.
LAYERS
(only updated if using LIFO or FIFO)
Records are deleted if the item number starts with an asterisk. This denotes a miscellaneous item.
WAREHOUSE STATUS
All warehouse quantity fields are updated.
Normally, the fields in theses files/tables are updated during posting in Order Entry, Purchase Order, Inventory Control and Point of Sale. If posting is interrupted by power failure or equipment failure, inaccurate quantities could result in any of the above data sets.
The following input files are used to bring I/C information into sync with the other modules:
Accounts Receivable. This module is processed if the Use PBS I/C flag is checked (set to Y) on A/R Control information. The invoice transaction header and line item files are used to update the item file and the status file.
Order Entry. This module is processed if the O/E Control information is present, and the Back order control flag is checked (set to Y) in I/C Control information. The transaction header and line item files are used to update Items and Statuses, and I/C tracking information. (see more on updating the tracking information below).
Purchase Order. This module is processed if the Interface to I/C flag is set on the PO Control information to ‘Y’. The transaction header records and line items are used to update to I/C Items and Statuses.
Point of Sale. This module is processed in the same manner as Order Entry.
I/C Tracking has six types of records:
U - Unsold serial numbers
S - Sold serial numbers
L - Lot header records
D - Lot detail records
K - Kit definition records
R - Kit resequence work records
For synchronization purposes, only record types U and L are updated. As a first step, these records are re-set to show that no items are committed.
Synchronizing the tracking file is only done for O/E and P/S records, not for A/R or P/O. Each O/E or P/S line item record has a tracking code. A value of "A" or "S" means we are tracking serial numbers. A value of "B" or "D" means we are tracking lots. Note: Actually, in the item file, only “S” for serial and “D” for lot is allowed as entries.
Updating Serial Number Records
For each line item record with tracking flag "A" or "S", the matching I/C tracking record is processed as follows:
| • | Locate the type "U" I/C tracking record with matching order number and line sequence number. The record contains a status code. A code of blank means the serial number is available. In this case we will update the record by moving a code of "C", which means that the serial number is committed. If the code was not blank, an error message is issued that the serial number is committed more than once. |
| • | Serial number records are not updated for credits nor for line item records with a zero quantity to ship. |
Updating Lot Number Records
For each line item record with tracking flag "B" or "D", the matching I/C tracking record is processed as follows:
| • | Locate the type "L" I/C tracking record with matching item, warehouse, and lot number. The record is re-written with quantity committed updated by adding quantity to ship from the line item record (or subtracting in the case of a credit). |
From the File recovery utilities menu:
Select
6. Recalculate inventory quantities
The following screen displays:
Only quantities that are updated are displayed as follows:
| 1. | QUANTITY COMMITTED and QUANTITY ON BACK ORDER display if Order Entry is in use and Back order Control was selected in the I/C Control information. |
| 2. | QUANTITY ON ORDER displays if Purchase Order is in use. |
| 3. | QUANTITY ON HAND displays only if LIFO or FIFO costing is being used. (QUANTITY ON HAND is set from the Layer File.) |
| 4. | QUANTITY ON WORK ORDER displays only if kits are used and there are incomplete work orders on file. |
You are then asked, Are you sure you want to do this ?.
Answer Y or N.
If you answer N, the file recovery utilities menu appears. If Y, the fields in I/C are updated based upon data in O/E, P/O, I/C, P/S and A/R, as appropriate. The screen displays the number of the item that is being processed.
If any quantities are corrected, the Inventory Recalculation Exception Report prints.
If you are not interested with the inner workings of the program or technical terms then skip this section.
If you would like to understand when each file/table is checked and written too and in the order they are done this section is for you.
The program first opens the following files/tables:
Inventory Control information (ICCTLF)
Order Entry Control information (OECTLF) - to check if O/E is interfaced with I/C),
Point of Sale Control information (PSCTLF) - to check if P/S is interfaced with I/C),
Purchase Order Control information (POCTLF) - to check if P/O is interfaced with I/C), and
Accounts Receivable Control information (ARCTLF) - to check if A/R is interfaced with I/C.
Based on the control records found, the program opens the following files/tables:
The program opens the following: CNGHDR, ITMFIL, STAFIL, ICTRKF, LAYFIL (if using LIFO or FIFO), INVTRX, ORDHDR, LINITM, OETKXF, PIHDRF, PILINF, POHDRF, POLINF, RCVTRX, RECWRK, WKOCST, CMPFIL, POSHDR, POSLIN, PSTKXF.
Opened I-O: ITMFIL, ICTRKF
Before recalculating, the following checks are performed (anyone failing will exist the function):
| • | If using PO, checks to make sure there are no RCVTRX records. |
| • | If using PO, make sure there are no un-posted purchase orders on file by checking the CNGHDR file. |
| • | Make sure INVTRX is empty. |
| • | Make sure ICTRKF is empty. |
| • | Make sure the STAFIL is empty. |
| • | Make sure there is at least one non-miscellaneous item in the ITMFIL. |
Recalculating:
| • | Use a work file to create records based on STAFIL records. Creates 1 RECWRK record for each STAFIL record. |
| • | Un-commit unsold serial records (ICTRKF) by setting IC-TRK-SER-STAT from committed to available and adding 1 to the RECALC-TRACK-QTY-ON-HND for the matching RECWRK record. |
| • | Un-commit lot qtys. Using the ICTRKF, search for any lot header record, confirm the Item is a lot item, get the ICTRKF detail records for each ICTRKF lot header record and compute the total quantities on-hand, received, sold, credited, adjusted. If either total does not match the ICTRKF lot header record quantity, update the ICTRKF lot header quantity and set a flag to indicate an update was done. Add the ICTRKF lot header qty-on-hand to the RECALC-TRACK-QTY-ON-HND for the matching RECWRK record and if an update was done to the ICTRKF lot header record, add 1 to the RECALC-ADJ-TRACK-CNT for the matching RECWRK record. |
The below steps build up the recalculate work file (RECWRK).
| • | If LIFO or FIFO, the program updates based on LAYFIL records by warehouse or item. |
| • | If using kits, it updates based on qty-committed for kits components. |
| • | If using O/E, it updates based on qty-committed and qty-backordered based on OE LINITM records. |
| • | If using P/S, it updates based on qty-committed and qty-backordered based on PS POSLIN records. |
| • | If using A/R, it updates based on qty-committed and qty-backordered based on AR PILINF records. |
| • | If using P/O, it updates based on qty-committed and qty-backordered based on PO POLINF records. |
Other steps include:
| • | Close and open I-O the STAFIL and LAYFIL. |
Using the recalculate work file the program does the following:
| • | Prints the report and if any of the quantities changed, updates the STAFIL records accordingly. |
| • | Updates the ICTRKF file quantities for any lot records that changed creating correction records as needed and reports these changes. |
| • | Updates the ITMFIL record quantities accordingly reporting such changes. |
| • | Prints grand totals |
| • | It Initialize the recalculate work file and informs you the process is complete. |
This selection allows you to rebuild item keywords by reading the item description, sub-category, and vendor item number that are stored in I/C Items.
Select
7. Rebuild item keywords
The following screen displays:
Technical Information
It opens the Control information (ICCTLF) to confirm control information has been entered and item keyword search is requested.
Either failure will result in an appropriate message and exit this function.
Opens the item file/table to make sure there is at least one record. Display a message and exits this function if none are found.
Opens the Item keyword file/table.
Displays a screen showing what keyword field(s) will be updated based on the Control information item keyword search setting, i.e., Item description, Sub-category, Category / Sub-category, Vendor product #, Vendor # / Vendor product #.
Asks user to confirm processing and exits this function if user does not wish to proceed.
Deletes all types (9, i.e., 5 descriptions + 4 as above) of CKEYWF records.
Starts the ITMFIL from its first record sorted by item#.
For each item record found, up to 4 CKEYWF records may be written based on the 4 search item requested in Control information using the following:
| • | For any description type, the keyword field is upper-cased and the value used to key the CKEYWF record. |
| • | For other types, the value is used to key the CKEYWF record. |
When done a message "Process complete" is shown.
This selection allows you to rebuild the item serial keyword file by reading the item serial numbers that are stored in the I/C item serial file.
Select
8. Rebuild serial keywords
The following screen displays:
Techical Information
Opens the Serial Numbers (ICTRKF) file/table and confirms there is at least 1 serial record. If none are found an appropriate message is displayed and the process is ended.
Opens the serial keyword file.
Displays an information screen to indicate the keyword file is based on Item serial-#.
Asks user to confirm processing and exits this function if user does not wish to proceed.
Recreates the SKEYWF.
Starts the ICTRKF on its key from the first "S" type record.
For each record found, an "S" type SKEYWF record is created using the last 4 characters of the serial-#, upper-cased, as the token.
The item-# is used as the next item on the key and the full serial-# is stored in the body of this record.
When done a message "Process complete" is shown.
This selection reads existing item and status information to recreate history quantities.
This selection sets the Item and Status quantity committed fields based what was entered in the Transaction Line Items from O/E, P/S or A/R.
Select
9. Recalculate history quantities
The program immediately recalculates history quantities.
Technical Information
Opens the ITMFIL, STAFIL, ITXHIS and ICCTLF files.
Confirms an IC Control information record exists. If none found, an appropriate message is shown and the process is ended.
Displays an information screen showing STAFIL data, i.e., warehouse and item#.
Starts the STAFIL on its alternate key, item#+warehouse, from the first record.
Updates the screen warehouse and item-#
Phase I based on Status file as follow:
For each STAFIL record:
| • | start the ITXHIS file on its alternate key 2 at the last record matching warehouse and item# (load the date field with 99991231 and start less than and read previous). |
| • | read the file in reverse order to update the INV-TRX-HIST-BAL and INV-TRX-HIST-QTY-AVAIL from last to first record as follow: |
the first ITXHIS record for this warehouse/item# will get the STAT-QTY-ON-HND (running balance). This is latest dated record.
the following records' INV-TRX-HIST-BAL and INV-TRX-HIST-QTY-AVAIL will be updated by adding/subtracting the INV-TRX-HIST-QTY to/from the running balance based on the INV-TRX-HIST-TYP of the current record. This means the calculation is done before the previous record is read and updated. In other words, the INV-TRX-HIST-BAL and INV-TRX-HIST-QTY-AVAIL of a record is based on the type and quantity of the previously read record.
| • | Once all the ITXHIS records for that particular warehouse/item# combination have been processed, a new balance-forward (99) type ITXHIS record is created, dated 01/01/1980, "BALFWD" document#, "Balance forward" comment, previous record INV-TRX-HIST-ACTUAL-COST, posted with today's date, record to contain the remaining balance in INV-TRX-HIST-BAL, INV-TRX-HIST-QTY-AVAIL and INV-TRX-HIST-QTY. |
Phase II based on Item file as follow:
Starts the ITMFIL on its key from the first item.
For each item record found:
| • | start the ITXHIS file on its key at the last record matching item# (load the date field with 99991231 and start less than and read previous). |
| • | read the file in reverse order to update the INV-TRX-HIST-BAL from last to first record as follow: |
the first ITXHIS record for this item# will get the ITEM-QTY-ON-HND (running balance). This is latest dated record.
the following records' INV-TRX-HIST-BAL will be updated by adding/subtracting the INV-TRX-HIST-QTY to/from the running balance based on the INV-TRX-HIST-TYP of the current record. This means the calculation is done before the previous record is read and updated. In other words, the INV-TRX-HIST-BAL of a record is based on the type and quantity of the previously read record.
When done a message "Process complete" is shown.
This selection creates an inventory history by using existing item and status information, and offers the choice of using average or replacement costs.
Select
10. Create new inventory history
The following screen displays:
Enter A for average cost or R or replacement cost.
Technical Information
Opens the ITMFIL, STAFIL, ITXHIS and ICCTLF files.
Confirms an IC Control information record exists. If none found, an appropriate message is shown and the process is ended.
Asks user whether Average or Replacement cost should be used.
Asks Ok to proceed? Exit program if user does not want to proceed.
Displays screen to show warehouse and item processed.
Starts the STAFIL on its alternate key, item#+warehouse, from the first record.
Updates the screen warehouse and item-#.
For each STAFIL record:
| • | read the ITMFIL record and initialize the ITEM-REC if not found. |
| • | create a balance-forward (99) type ITXHIS record, dated today, "BALFWD" document#, "Balance forward" comment, ITEM-QTY-ON-HND for INV-TRX-HIST-BAL, STAT-QTY-ON-HND for INV-TRX-HIST-QTY-AVAIL and INV-TRX-HIST-QTY, using either ITEM-RPLC-COST or ITEM-AVG-COST based on user's wish for the INV-TRX-HIST-ACTUAL-COST, posted with today's date. |
When done a message "Process complete" is shown.